package com.yun2s.tools.excel;

import java.io.File;
import java.io.FileInputStream;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

/**
 * EXCEL 数据操作类
 * @author "fanhm"
 *
 */
public class ExcelOptTools {

	/**
	 * 读取excel文件的填入的数据
	 * @param excelFilePath
	 * @param paraIndexs
	 * @param sheetFlag
	 * @return
	 */
	private static StringBuffer getDataFromExcel(String excelFilePath,String[] paraIndexs,int sheetFlag){
		//从excel里读取数据源头
		List<ExcelDataVO> dataStrList = new ArrayList<ExcelDataVO>();
		ExcelDataVO excelDataVO = new ExcelDataVO();
		HSSFWorkbook workbook = new HSSFWorkbook();
		String clumnName;
		String columnValue  = "";
		HSSFSheet sheet = null;
		HSSFRow row = null;
		HSSFCell cell = null;
		String columnType = "STRING";
		StringBuffer sbResult = new StringBuffer();
		File uploadFile = new File(excelFilePath);
		try {
			FileInputStream fileInputStream = new FileInputStream(uploadFile);
			workbook = new HSSFWorkbook(fileInputStream);
		} catch (Exception e) {
			
		}
		sheet = workbook.getSheetAt(sheetFlag-1);
		int rows = sheet.getPhysicalNumberOfRows();
		
		  HSSFCellStyle cellStyle = workbook.createCellStyle();
		  HSSFDataFormat format = workbook.createDataFormat();
		  cellStyle.setDataFormat(format.getFormat("@"));

		
		
		for(int j=1;j<rows;j++){
			row =  sheet.getRow(j);
			clumnName = row.getCell(1)==null?"":row.getCell(1).toString();
			cell = row.getCell(5);
			columnValue = "";
			if(cell!=null){
				if(cell.getCellType() == cell.CELL_TYPE_NUMERIC){
					DecimalFormat df = new DecimalFormat("#");
					columnValue = df.format(cell.getNumericCellValue());
				}else{
					columnValue = cell.toString();
				}
			}
			excelDataVO = new ExcelDataVO(clumnName,columnValue);
			dataStrList.add(excelDataVO);
		} 
		
		//取到数据后根据列表顺序输出
		out:
		for(String str :paraIndexs){
			if(str==null){
				System.out.println(paraIndexs.toString());
				continue;
			}
			for(ExcelDataVO vo :dataStrList){
				if(str.equals(vo.getColumnName().trim())){
					sbResult.append(str+"="+vo.getColumnValue()+", ");
					continue out;
				}
			}
			//如果运行到这里说明没有找到对应值
			sbResult.append(str+"=, ");
		}
		sbResult.delete(sbResult.length()-",".length()-1, sbResult.length()-1);
		return sbResult;
	}
	
	
	
	
	
	
	
	
	
	
	
	/**
	 * @param args
	 */
	public static void main(String[] args) {
		// TODO Auto-generated method stub

	}

}
